<!DOCTYPE html>



  


<html class="theme-next mist use-motion" lang="zh-Hans">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>
<meta name="theme-color" content="#222">


  
  
    
    
  <script src="/lib/pace/pace.min.js?v=1.0.2"></script>
  <link href="/lib/pace/pace-theme-minimal.min.css?v=1.0.2" rel="stylesheet">







<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />
















  
  
  <link href="/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />







<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.1.4" rel="stylesheet" type="text/css" />


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon.ico?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon.ico?v=5.1.4">


  <link rel="mask-icon" href="/images/logo.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="mysql,存储过程," />










<meta name="description" content="统计时间段查询123456789101112131415161718192021222324252627282930CREATE PROCEDURE PROC_REPORT_SELECT_COUNT(IN parlorId INT)#0:报告数，1:阅读量，2:点评量，3:顾客数BEGIN  SELECT    Today.detectionCount as today_detection,Tod">
<meta name="keywords" content="mysql,存储过程">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql存储过程花式查询">
<meta property="og:url" content="http://tomasyao.gitee.info/2017/07/27/mysql存储过程花式查询/index.html">
<meta property="og:site_name" content="Tomasyao">
<meta property="og:description" content="统计时间段查询123456789101112131415161718192021222324252627282930CREATE PROCEDURE PROC_REPORT_SELECT_COUNT(IN parlorId INT)#0:报告数，1:阅读量，2:点评量，3:顾客数BEGIN  SELECT    Today.detectionCount as today_detection,Tod">
<meta property="og:locale" content="zh-Hans">
<meta property="og:updated_time" content="2018-11-30T04:02:41.068Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="mysql存储过程花式查询">
<meta name="twitter:description" content="统计时间段查询123456789101112131415161718192021222324252627282930CREATE PROCEDURE PROC_REPORT_SELECT_COUNT(IN parlorId INT)#0:报告数，1:阅读量，2:点评量，3:顾客数BEGIN  SELECT    Today.detectionCount as today_detection,Tod">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Mist',
    version: '5.1.4',
    sidebar: {"position":"left","display":"hide","offset":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="http://tomasyao.gitee.info/2017/07/27/mysql存储过程花式查询/"/>





  <title>mysql存储过程花式查询 | Tomasyao</title>
  








</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/"  class="brand" rel="start">
        <span class="logo-line-before">
          <i style="background:rgba(204,163,81,0.8);height:2px;"></i>
        </span>
        <span class="site-title">Tomasyao</span>
        <span class="logo-line-after">
          <i style="background:rgba(204,163,81,0.8);height:2px;"></i>
        </span>
      </a>
    </div>
      
        <p class="site-subtitle">与世界分享我刚编的故事</p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      
        
        <li class="menu-item menu-item-commonweal">
          <a href="/404.html" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-heartbeat"></i> <br />
            
            公益404
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="http://tomasyao.gitee.info/2017/07/27/mysql存储过程花式查询/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="tomasyao">
      <meta itemprop="description" content="">
      <meta itemprop="image" content="http://pic.tomasyao.info/avatar.png">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Tomasyao">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">mysql存储过程花式查询</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2017-07-27T09:11:28+08:00">
                2017-07-27
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          
            <div class="post-wordcount">
              
                
                <span class="post-meta-item-icon">
                  <i class="fa fa-file-word-o"></i>
                </span>
                
                  <span class="post-meta-item-text">字数统计&#58;</span>
                
                <span title="字数统计">
                  1,708
                </span>
              

              
                <span class="post-meta-divider">|</span>
              

              
                <span class="post-meta-item-icon">
                  <i class="fa fa-clock-o"></i>
                </span>
                
                  <span class="post-meta-item-text">阅读时长 &asymp;</span>
                
                <span title="阅读时长">
                  9
                </span>
              
            </div>
          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <h2 id="统计时间段查询"><a href="#统计时间段查询" class="headerlink" title="统计时间段查询"></a>统计时间段查询</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div></pre></td><td class="code"><pre><div class="line">CREATE PROCEDURE PROC_REPORT_SELECT_COUNT(IN parlorId INT)#0:报告数，1:阅读量，2:点评量，3:顾客数</div><div class="line">BEGIN</div><div class="line">  SELECT</div><div class="line">    Today.detectionCount as today_detection,Today.viewCount as today_view,Today.reviewCount as today_reviewCount,Today.customerCount as today_customer,</div><div class="line">    SevenDay.detectionCount as day7_detection,SevenDay.viewCount as day7_view,SevenDay.reviewCount as day7_reviewCount,SevenDay.customerCount as day7_customer,</div><div class="line">    ThirtyDay.detectionCount as day30_detection,ThirtyDay.viewCount as day30_view,ThirtyDay.reviewCount as day30_reviewCount,ThirtyDay.customerCount as day30_customer,</div><div class="line">    SixtyDay.detectionCount as day60_detection,SixtyDay.viewCount as day60_view,SixtyDay.reviewCount as day60_reviewCount,SixtyDay.customerCount as day60_customer,</div><div class="line">    AllDay.detectionCount as allday_detection,AllDay.viewCount as allday_view,AllDay.reviewCount as allday_reviewCount,AllDay.customerCount as allday_customer</div><div class="line">  FROM</div><div class="line">  #查询今天的0:报告数，1:阅读量，2:点评量，3:顾客数</div><div class="line">  (SELECT count(*) as detectionCount,sum(view) as viewCount,sum(review) as reviewCount,count(DISTINCT customer_id) as customerCount FROM detection</div><div class="line">    WHERE parlor_id=parlorId AND update_time IS NOT NULL</div><div class="line">    AND create_time&gt;=CURRENT_DATE() AND create_time&lt;DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY)) as Today,</div><div class="line">  #查询最近7天的0:报告数，1:阅读量，2:点评量，3:顾客数</div><div class="line">  (SELECT count(*) as detectionCount,sum(view) as viewCount,sum(review) as reviewCount,count(DISTINCT customer_id) as customerCount FROM detection</div><div class="line">    WHERE parlor_id=parlorId AND update_time IS NOT NULL</div><div class="line">    AND create_time&gt;=DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY) AND create_time&lt;DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY)) as SevenDay,</div><div class="line">  #查询最近一个月的0:报告数，1:阅读量，2:点评量，3:顾客数</div><div class="line">  (SELECT count(*) as detectionCount,sum(view) as viewCount,sum(review) as reviewCount,count(DISTINCT customer_id) as customerCount FROM detection</div><div class="line">    WHERE parlor_id=parlorId AND update_time IS NOT NULL</div><div class="line">    AND create_time&gt;=DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH) AND create_time&lt;DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY)) as ThirtyDay,</div><div class="line">  #查询最60天的0:报告数，1:阅读量，2:点评量，3:顾客数</div><div class="line">  (SELECT count(*) as detectionCount,sum(view) as viewCount,sum(review) as reviewCount,count(DISTINCT customer_id) as customerCount FROM detection</div><div class="line">    WHERE parlor_id=parlorId AND update_time IS NOT NULL</div><div class="line">    AND create_time&gt;=DATE_SUB(CURRENT_DATE(),INTERVAL 60 DAY) AND create_time&lt;DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY)) as SixtyDay,</div><div class="line">  #查询累计的0:报告数，1:阅读量，2:点评量，3:顾客数</div><div class="line">  (SELECT count(*) as detectionCount,sum(view) as viewCount,sum(review) as reviewCount,count(DISTINCT customer_id) as customerCount FROM detection</div><div class="line">    WHERE parlor_id=parlorId AND update_time IS NOT NULL</div><div class="line">    AND create_time&lt;DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY)) as AllDay;</div><div class="line">END;</div></pre></td></tr></table></figure>
<a id="more"></a>
<h2 id="统计每天结果并存储到新表"><a href="#统计每天结果并存储到新表" class="headerlink" title="统计每天结果并存储到新表"></a>统计每天结果并存储到新表</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div></pre></td><td class="code"><pre><div class="line">CREATE PROCEDURE PROC_REPORT_CREATE_REPORT_SUBWHILE(IN parlorId INT)#针对单个美容院统计每天的各项数据</div><div class="line">  BEGIN</div><div class="line">    DECLARE i INT;</div><div class="line">    SET i=2;#从昨天开始，直到最初那天</div><div class="line">    WHILE (i&lt;=200) DO</div><div class="line">        INSERT INTO data_statics(create_time,report_count,view_count,review_count,customer_count,count_time,parlor_id)</div><div class="line">          (SELECT NOW(),count(*),sum(view),sum(review),count(DISTINCT customer_id),DATE_SUB(CURRENT_DATE(),INTERVAL i-1 DAY),parlorId</div><div class="line">            FROM detection</div><div class="line">            WHERE parlor_id=parlorId AND update_time IS NOT NULL</div><div class="line">            AND create_time&gt;=DATE_SUB(CURRENT_DATE(),INTERVAL i-1 DAY) AND</div><div class="line">            create_time&lt;DATE_SUB(CURRENT_DATE(),INTERVAL i-2 DAY)</div><div class="line">          );</div><div class="line">        SET i=i+1;</div><div class="line">    END WHILE;</div><div class="line">  END;</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">CREATE PROCEDURE PROC_REPORT_CREATE_REPORT_MAINWHILE()#查询出所有美容院,然后调用上面的存储过程</div><div class="line">  BEGIN</div><div class="line">    DECLARE i INT;</div><div class="line">    DECLARE j INT;</div><div class="line">    SET i=0;</div><div class="line">    SET j=(SELECT count(*) FROM parlor WHERE deleted=0);</div><div class="line">    WHILE (i&lt;j) DO</div><div class="line">      CALL PROC_REPORT_CREATE_REPORT_SUBWHILE((SELECT id FROM parlor WHERE deleted=0 LIMIT i,1));</div><div class="line">      SET i=i+1;</div><div class="line">    END WHILE;</div><div class="line">  END;</div></pre></td></tr></table></figure>
<h2 id="统计所有业务人员各种业务操作的数量"><a href="#统计所有业务人员各种业务操作的数量" class="headerlink" title="统计所有业务人员各种业务操作的数量"></a>统计所有业务人员各种业务操作的数量</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div><div class="line">50</div><div class="line">51</div><div class="line">52</div><div class="line">53</div><div class="line">54</div><div class="line">55</div><div class="line">56</div><div class="line">57</div><div class="line">58</div><div class="line">59</div><div class="line">60</div><div class="line">61</div><div class="line">62</div><div class="line">63</div><div class="line">64</div><div class="line">65</div><div class="line">66</div><div class="line">67</div><div class="line">68</div><div class="line">69</div><div class="line">70</div></pre></td><td class="code"><pre><div class="line">CREATE PROCEDURE PROC_STUFF_HANDLE_SELECT_COUNUT(IN parlorId INT,IN startDate DATE,IN endDate DATE)#某一美容院任意时间段所有业务员的各种业务量统计</div><div class="line">  BEGIN</div><div class="line">    #发送短信 更改标签 淘客 添加备注 潜客 评价报告</div><div class="line">    SELECT t1.stuff_name,t1.amCount,t2.tkCount,t3.smsgCount,t4.etCount,t5.qiankeCount,t6.crCount</div><div class="line">    FROM</div><div class="line">    (SELECT sn.stuff_name,ifnull(addMark.amCount,0) AS amCount,ifnull(addMark.handle_detail,&apos;添加备注&apos;) AS hd</div><div class="line">     FROM</div><div class="line">      (SELECT DISTINCT(stuff_name) AS stuff_name FROM stuff_handle_logs) AS sn LEFT JOIN</div><div class="line">      (SELECT count(*) AS amCount,handle_detail,stuff_name</div><div class="line">          FROM stuff_handle_logs</div><div class="line">          WHERE parlor_id=parlorId AND handle_detail=&apos;添加备注&apos; AND stuff_name IN (SELECT DISTINCT(stuff_name) FROM stuff_handle_logs)</div><div class="line">            AND create_time&gt;=startDate AND create_time&lt;DATE_ADD(endDate,INTERVAL 1 DAY)</div><div class="line">          GROUP BY stuff_name</div><div class="line">      ) as addMark on sn.stuff_name=addMark.stuff_name</div><div class="line">    ) as t1,</div><div class="line">    (SELECT sn.stuff_name,ifnull(taoke.tkCount,0) AS tkCount,ifnull(taoke.handle_detail,&apos;淘客&apos;) AS hd</div><div class="line">     FROM</div><div class="line">      (SELECT DISTINCT(stuff_name) AS stuff_name FROM stuff_handle_logs) AS sn LEFT JOIN</div><div class="line">      (SELECT count(*) AS tkCount,handle_detail,stuff_name</div><div class="line">          FROM stuff_handle_logs</div><div class="line">          WHERE parlor_id=parlorId AND handle_detail=&apos;淘客&apos; AND stuff_name IN (SELECT DISTINCT(stuff_name) FROM stuff_handle_logs)</div><div class="line">            AND create_time&gt;=startDate AND create_time&lt;DATE_ADD(endDate,INTERVAL 1 DAY)</div><div class="line">          GROUP BY stuff_name</div><div class="line">      ) as taoke on sn.stuff_name=taoke.stuff_name</div><div class="line">    ) as t2,</div><div class="line">    (SELECT sn.stuff_name,ifnull(sendSMS.smsgCount,0) AS smsgCount,ifnull(sendSMS.handle_detail,&apos;发送短信&apos;) AS hd</div><div class="line">     FROM</div><div class="line">      (SELECT DISTINCT(stuff_name) AS stuff_name FROM stuff_handle_logs) AS sn LEFT JOIN</div><div class="line">      (SELECT count(*) AS smsgCount,handle_detail,stuff_name</div><div class="line">          FROM stuff_handle_logs</div><div class="line">          WHERE parlor_id=parlorId AND handle_detail=&apos;发送短信&apos; AND stuff_name IN (SELECT DISTINCT(stuff_name) FROM stuff_handle_logs)</div><div class="line">            AND create_time&gt;=startDate AND create_time&lt;DATE_ADD(endDate,INTERVAL 1 DAY)</div><div class="line">          GROUP BY stuff_name</div><div class="line">      ) as sendSMS on sn.stuff_name=sendSMS.stuff_name</div><div class="line">    ) as t3,</div><div class="line">    (SELECT sn.stuff_name,ifnull(editTag.etCount,0) AS etCount,ifnull(editTag.handle_detail,&apos;更改标签&apos;) AS hd</div><div class="line">     FROM</div><div class="line">      (SELECT DISTINCT(stuff_name) AS stuff_name FROM stuff_handle_logs) AS sn LEFT JOIN</div><div class="line">      (SELECT count(*) AS etCount,handle_detail,stuff_name</div><div class="line">          FROM stuff_handle_logs</div><div class="line">          WHERE parlor_id=parlorId AND handle_detail=&apos;更改标签&apos; AND stuff_name IN (SELECT DISTINCT(stuff_name) FROM stuff_handle_logs)</div><div class="line">            AND create_time&gt;=startDate AND create_time&lt;DATE_ADD(endDate,INTERVAL 1 DAY)</div><div class="line">          GROUP BY stuff_name</div><div class="line">      ) as editTag on sn.stuff_name=editTag.stuff_name</div><div class="line">    ) as t4</div><div class="line">    (SELECT sn.stuff_name,ifnull(qianke.qiankeCount,0) AS qiankeCount,ifnull(qianke.handle_detail,&apos;潜客&apos;) AS hd</div><div class="line">     FROM</div><div class="line">      (SELECT DISTINCT(stuff_name) AS stuff_name FROM stuff_handle_logs) AS sn LEFT JOIN</div><div class="line">      (SELECT count(*) AS qiankeCount,handle_detail,stuff_name</div><div class="line">          FROM stuff_handle_logs</div><div class="line">          WHERE parlor_id=parlorId AND handle_detail=&apos;潜客&apos; AND stuff_name IN (SELECT DISTINCT(stuff_name) FROM stuff_handle_logs)</div><div class="line">            AND create_time&gt;=startDate AND create_time&lt;DATE_ADD(endDate,INTERVAL 1 DAY)</div><div class="line">          GROUP BY stuff_name</div><div class="line">      ) as qianke on sn.stuff_name=qianke.stuff_name</div><div class="line">    ) as t5,</div><div class="line">    (SELECT sn.stuff_name,ifnull(commentReport.crCount,0) AS crCount,ifnull(commentReport.handle_detail,&apos;评价报告&apos;) AS hd</div><div class="line">     FROM</div><div class="line">      (SELECT DISTINCT(stuff_name) AS stuff_name FROM stuff_handle_logs) AS sn LEFT JOIN</div><div class="line">      (SELECT count(*) AS crCount,handle_detail,stuff_name</div><div class="line">          FROM stuff_handle_logs</div><div class="line">          WHERE parlor_id=parlorId AND handle_detail=&apos;评价报告&apos; AND stuff_name IN (SELECT DISTINCT(stuff_name) FROM stuff_handle_logs)</div><div class="line">            AND create_time&gt;=startDate AND create_time&lt;DATE_ADD(endDate,INTERVAL 1 DAY)</div><div class="line">          GROUP BY stuff_name</div><div class="line">      ) as commentReport on sn.stuff_name=commentReport.stuff_name</div><div class="line">    ) as t6</div><div class="line">    WHERE t1.stuff_name=t2.stuff_name AND t1.stuff_name=t3.stuff_name AND t1.stuff_name=t4.stuff_name AND t1.stuff_name=t5.stuff_name</div><div class="line">          AND t1.stuff_name=t6.stuff_name AND t2.stuff_name=t3.stuff_name AND t2.stuff_name=t4.stuff_name AND t2.stuff_name=t5.stuff_name</div><div class="line">          AND t2.stuff_name=t6.stuff_name AND t3.stuff_name=t4.stuff_name AND t3.stuff_name=t5.stuff_name AND t3.stuff_name=t6.stuff_name</div><div class="line">          AND t4.stuff_name=t5.stuff_name AND t4.stuff_name=t6.stuff_name AND t5.stuff_name=t6.stuff_name;</div><div class="line">  END;</div></pre></td></tr></table></figure>
<h2 id="定时任务每天凌晨3点统计昨天情况"><a href="#定时任务每天凌晨3点统计昨天情况" class="headerlink" title="定时任务每天凌晨3点统计昨天情况"></a>定时任务每天凌晨3点统计昨天情况</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line">CREATE PROCEDURE PROC_REPORT_CREATE_REPORT_SUBWHILE_YESTODY(IN parlorId INT)#针对单个美容院统计昨天的各项数据(event在凌晨执行)</div><div class="line">  BEGIN</div><div class="line">    INSERT INTO data_statics(create_time,report_count,view_count,review_count,customer_count,count_time,parlor_id)</div><div class="line">      (SELECT NOW(),count(*),sum(view),sum(review),count(DISTINCT customer_id),DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY),parlorId</div><div class="line">        FROM detection</div><div class="line">        WHERE parlor_id=parlorId AND update_time IS NOT NULL</div><div class="line">        AND create_time&gt;=DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY) AND</div><div class="line">        create_time&lt;CURRENT_DATE()</div><div class="line">      );</div><div class="line">  END;</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">CREATE PROCEDURE PROC_REPORT_CREATE_REPORT_MAINWHILE_YESTODY()#查询出所有美容院,然后调用上面的存储过程(针对昨天的)</div><div class="line">  BEGIN</div><div class="line">    DECLARE i INT;</div><div class="line">    DECLARE j INT;</div><div class="line">    SET i=0;</div><div class="line">    SET j=(SELECT count(*) FROM parlor WHERE deleted=0);</div><div class="line">    WHILE (i&lt;j) DO</div><div class="line">      CALL PROC_REPORT_CREATE_REPORT_SUBWHILE_YESTODY((SELECT id FROM parlor WHERE deleted=0 LIMIT i,1));</div><div class="line">      SET i=i+1;</div><div class="line">    END WHILE;</div><div class="line">  END;</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">CREATE EVENT EVENT_INSERT_YESTODAY_COUNT #每天凌晨3点执行一次</div><div class="line">  ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE(CURRENT_DATE() + 1),interval 3 hour)</div><div class="line">  ON COMPLETION PRESERVE</div><div class="line">  DO CALL PROC_REPORT_CREATE_REPORT_MAINWHILE_YESTODY();</div></pre></td></tr></table></figure>
<h2 id="定时任务状态查看"><a href="#定时任务状态查看" class="headerlink" title="定时任务状态查看"></a>定时任务状态查看</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">SHOW VARIABLES LIKE &apos;event_scheduler&apos;;</div><div class="line">SET GLOBAL event_scheduler = &apos;ON&apos;;</div></pre></td></tr></table></figure>
<h2 id="存储过程调用"><a href="#存储过程调用" class="headerlink" title="存储过程调用"></a>存储过程调用</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">CALL PROC_REPORT_SELECT_COUNT(83);</div><div class="line">CALL PROC_REPORT_CREATE_REPORT_MAINWHILE();#一次性的，以后用定时任务，在每天凌晨3点统计每个美容院前一天的数据即可</div><div class="line">CALL PROC_REPORT_CREATE_REPORT_MAINWHILE_YESTODY();</div><div class="line">CALL PROC_STUFF_HANDLE_SELECT_COUNUT(83,STR_TO_DATE(&apos;2017-08-01&apos;, &apos;%Y-%m-%d&apos;),STR_TO_DATE(&apos;2017-08-13&apos;, &apos;%Y-%m-%d&apos;));</div></pre></td></tr></table></figure>
<h2 id="另mysql系统日期函数"><a href="#另mysql系统日期函数" class="headerlink" title="另mysql系统日期函数"></a>另mysql系统日期函数</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div></pre></td><td class="code"><pre><div class="line">################操作日期2017-07-24#########</div><div class="line">SELECT CURRENT_DATE(); #2017-07-24 当天日期</div><div class="line">SELECT CURDATE(); #2017-07-24 当天日期</div><div class="line">SELECT CURRENT_TIME(); #11:25:52 当前时间</div><div class="line">SELECT CURTIME(); #11:25:52 当前时间</div><div class="line">SELECT NOW(); #2017-07-24 11:25:52 当前 日期+时间</div><div class="line">#SELECT DATE_SUB(date,INTERVAL int keyword);</div><div class="line">SELECT DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY); #2017-07-23 昨天</div><div class="line">SELECT DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH); #2017-06-24 上个月这天</div><div class="line">SELECT DATE_SUB(CURRENT_DATE(),INTERVAL 1 YEAR); #2016-07-24 去年今天</div><div class="line">SELECT DATE_SUB(CURRENT_DATE(),INTERVAL &apos;1-1&apos; YEAR_MONTH); #2016-06-24 1年1个月以前</div><div class="line">#SELECT DATE_ADD(date,INTERVAL int keyword);</div><div class="line">SELECT DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY); #2017-07-25 明天</div><div class="line">SELECT DATE_ADD(CURRENT_DATE(),INTERVAL 1 MONTH); #2017-08-24 下个月这天</div><div class="line">SELECT DATE_ADD(CURRENT_DATE(),INTERVAL 1 YEAR); #2018-07-24 明年今天</div><div class="line">SELECT DATE_ADD(CURRENT_DATE(),INTERVAL &apos;1-1&apos; YEAR_MONTH); #2018-08-24 1年1个月以后</div><div class="line">################字符串转日期 2017-07-24#########</div><div class="line">SELECT STR_TO_DATE(&apos;2016-01-02&apos;, &apos;%Y-%m-%d&apos;);</div></pre></td></tr></table></figure>
      
    </div>
    
    
    
    
        <div>
        
    <div style="text-align:center;color: #ccc;font-size:14px;">-------------本文结束<i class="fa fa-paw"></i>感谢您的阅读-------------</div>


      </div>
    

    

    
      <div>
        <div style="padding: 10px 0; margin: 20px auto; width: 90%; text-align: center;">
  <div>坚持原创技术分享，您的支持将鼓励我继续创作！</div>
  <button id="rewardButton" disable="enable" onclick="var qr = document.getElementById('QR'); if (qr.style.display === 'none') {qr.style.display='block';} else {qr.style.display='none'}">
    <span>打赏</span>
  </button>
  <div id="QR" style="display: none;">

    
      <div id="wechat" style="display: inline-block">
        <img id="wechat_qr" src="http://pic.tomasyao.info/lALPACOG81QLSKnNBHDNAzw_828_1136.png_620x10000q90g.jpg" alt="tomasyao 微信支付"/>
        <p>微信支付</p>
      </div>
    

    
      <div id="alipay" style="display: inline-block">
        <img id="alipay_qr" src="http://pic.tomasyao.info/lADPACOG81QJkZHNBETNAtA_720_1092.jpg_620x10000q90g.jpg" alt="tomasyao 支付宝"/>
        <p>支付宝</p>
      </div>
    

    

  </div>
</div>

      </div>
    

    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/mysql/" rel="tag"><i class="fa fa-tag"></i> mysql</a>
          
            <a href="/tags/存储过程/" rel="tag"><i class="fa fa-tag"></i> 存储过程</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2017/07/19/ubuntu14-04扁平化主题flatabulous/" rel="next" title="ubuntu14.04扁平化主题flatabulous">
                <i class="fa fa-chevron-left"></i> ubuntu14.04扁平化主题flatabulous
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2017/08/02/linux偏僻操作/" rel="prev" title="linux偏僻操作">
                linux偏僻操作 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <img class="site-author-image" itemprop="image"
                src="http://pic.tomasyao.info/avatar.png"
                alt="tomasyao" />
            
              <p class="site-author-name" itemprop="name">tomasyao</p>
              <p class="site-description motion-element" itemprop="description">D9lab</p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/archives">
              
                  <span class="site-state-item-count">26</span>
                  <span class="site-state-item-name">日志</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-categories">
                <a href="/categories/index.html">
                  <span class="site-state-item-count">1</span>
                  <span class="site-state-item-name">分类</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-tags">
                <a href="/tags/index.html">
                  <span class="site-state-item-count">39</span>
                  <span class="site-state-item-name">标签</span>
                </a>
              </div>
            

          </nav>

          

          

          
          

          
          
            <div class="links-of-blogroll motion-element links-of-blogroll-inline">
              <div class="links-of-blogroll-title">
                <i class="fa  fa-fw fa-link"></i>
                Links
              </div>
              <ul class="links-of-blogroll-list">
                
                  <li class="links-of-blogroll-item">
                    <a href="http://www.uisdc.com/" title="优设" target="_blank">优设</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://www.zhangxinxu.com/" title="张鑫旭" target="_blank">张鑫旭</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://www.alloyteam.com/nav/" title="Web前端导航" target="_blank">Web前端导航</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://www.36zhen.com/t?id=3448" title="前端书籍资料" target="_blank">前端书籍资料</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://ife.baidu.com/" title="百度前端技术学院" target="_blank">百度前端技术学院</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://wf.uisdc.com/cn/" title="google前端开发基础" target="_blank">google前端开发基础</a>
                  </li>
                
              </ul>
            </div>
          

          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#统计时间段查询"><span class="nav-number">1.</span> <span class="nav-text">统计时间段查询</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#统计每天结果并存储到新表"><span class="nav-number">2.</span> <span class="nav-text">统计每天结果并存储到新表</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#统计所有业务人员各种业务操作的数量"><span class="nav-number">3.</span> <span class="nav-text">统计所有业务人员各种业务操作的数量</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#定时任务每天凌晨3点统计昨天情况"><span class="nav-number">4.</span> <span class="nav-text">定时任务每天凌晨3点统计昨天情况</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#定时任务状态查看"><span class="nav-number">5.</span> <span class="nav-text">定时任务状态查看</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#存储过程调用"><span class="nav-number">6.</span> <span class="nav-text">存储过程调用</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#另mysql系统日期函数"><span class="nav-number">7.</span> <span class="nav-text">另mysql系统日期函数</span></a></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <script async src="https://dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js"></script>
<div class="copyright">&copy; <span itemprop="copyrightYear">2018</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">tomasyao</span>

  
</div>


  <div class="powered-by">
  <i class="fa fa-user-md"></i><span id="busuanzi_container_site_uv">
    本站访客数:<span id="busuanzi_value_site_uv"></span>
  </span>
  </div>



  <span class="post-meta-divider">|</span>



  <div class="powered-by">由 <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> 强力驱动</div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">主题 &mdash; <a class="theme-link" target="_blank" href="https://github.com/iissnan/hexo-theme-next">NexT.Mist</a> v5.1.4</div>





  <span class="post-meta-divider">|</span>



  <div class="theme-info">
    <div class="powered-by"></div>
    <span class="post-count">博客全站共16.8k字</span>
  </div>


        







        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  












  
  
    <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.1.4"></script>



  
  

  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.4"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.1.4"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.4"></script>



  


  




	





  





  












  





  

  

  

  
  

  

  

  

  <!-- 页面点击小红心 -->
  <script type="text/javascript" src="/js/src/love.js"></script>
<script src="/live2dw/lib/L2Dwidget.min.js?0c58a1486de42ac6cc1c59c7d98ae887"></script><script>L2Dwidget.init({"model":{"scale":1,"hHeadPos":0.5,"vHeadPos":0.618,"jsonPath":"/live2dw/assets/tororo.model.json"},"display":{"superSample":2,"width":80,"height":100,"position":"right","hOffset":30,"vOffset":10},"mobile":{"show":true,"scale":0.5},"react":{"opacityDefault":1,"opacityOnHover":1},"log":false,"pluginJsPath":"lib/","pluginModelPath":"assets/","pluginRootPath":"live2dw/","tagMode":false});</script></body>
</html>
